CREATE VIEW [dbo].[vModuleOfferedCustomerExperience]
AS
SELECT
    [dbo].[OfferedCustomerExperience].[OfferedCustomerExperienceKey],
    [dbo].[Offering].[OfferingKey],
    [dbo].[CertificationModule].[RequiresGradeFlag],
    [dbo].[GradeRef].[GradeValue] AS OfferedCustomerExperienceGrade,
    [dbo].[CertificationModule].[CertificationModuleKey],
    [dbo].[CertificationModule].[CertificationModuleName],
    [dbo].[CertificationModule].[NumberOfUnits],
    [dbo].[Offering].[OfferingName],
    [dbo].[Offering].[OfferingCode],
    [dbo].[Offering].[OfferingDesc],
    [dbo].[Offering].[ContactKey],
    [dbo].[Offering].[LocationKey],
    [dbo].[Offering].[OfferingDate],
    [dbo].[CertificationProgram].[CertificationProgramKey],
    [dbo].[CertificationProgram].[CertificationProgramName],
    ISNULL([dbo].[Location].[LocationName], N'') AS LocationName
FROM
    [dbo].[CertificationProgramRegistration]
        INNER JOIN [dbo].[CertificationModuleRegistration] ON [dbo].[CertificationProgramRegistration].[CertificationProgramRegistrationKey] = [dbo].[CertificationModuleRegistration].[CertificationProgramRegistrationKey]
        INNER JOIN [dbo].[CertificationProgram] ON [dbo].[CertificationProgramRegistration].[CertificationProgramKey] = [dbo].[CertificationProgram].[CertificationProgramKey]
        INNER JOIN [dbo].[OfferedCustomerExperience] INNER JOIN [dbo].[Offering] ON [dbo].[OfferedCustomerExperience].[OfferingKey] = [dbo].[Offering].[OfferingKey]
        INNER JOIN [dbo].[CertificationModule] ON [dbo].[Offering].[ExperienceDefinitionKey] = [dbo].[CertificationModule].[CertificationModuleKey] ON [dbo].[CertificationModuleRegistration].[CustomerExperienceKey] = [dbo].[OfferedCustomerExperience].[OfferedCustomerExperienceKey]
        LEFT OUTER JOIN [dbo].[Location] ON [dbo].[Offering].[LocationKey] = [dbo].[Location].[LocationKey]
        LEFT OUTER JOIN [dbo].[GradeRef] ON [dbo].[OfferedCustomerExperience].[OfferedCustomerExperienceGradeKey] = [dbo].[GradeRef].[GradeKey]

GO